package sms.dao;

import sms.entry.ChengJi;
import sms.entry.KeCheng;
import sms.entry.XueSheng;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;


public class ChengJiDao {
    public void save(XueSheng xueSheng,KeCheng keCheng,ChengJi chengJi){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc","root","zhuchao");
            //根据学生名字获取id
            String sql1 = " select id from xueshengxinxi where name=? ";
            PreparedStatement stmt1 = connection.prepareStatement(sql1);
            stmt1.setObject(1,xueSheng.getName());
            ResultSet rs = stmt1.executeQuery();
            rs.next();
            Object xuesheng_id = rs.getObject("id");
            //根据科目名称获取id
            String sql2 = " select id from kecheng where km=? ";
            PreparedStatement stmt2 = connection.prepareStatement(sql2);
            stmt2.setObject(1,keCheng.getKm());
            ResultSet rs2 = stmt2.executeQuery();
            rs2.next();
            Object km_id = rs2.getObject("id");
            //将学生id和科目id添加到成绩表中
            String sql3=" insert into chengji (km_id,xuesheng_id,fenshu) VALUES (?,?,?)  ";
            PreparedStatement stmt3 = connection.prepareStatement(sql3);
            stmt3.setObject(1,km_id);
            stmt3.setObject(2, xuesheng_id);
            stmt3.setObject(3,chengJi.getFenshu());
            stmt3.executeUpdate();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public void remover(ChengJi chengJi){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc","root","zhuchao");
            String sql=" delete  from chengji where xuesheng_id=? and km_id=? ";
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setObject(1,chengJi.getXuesheng_id());
            stmt.setObject(2, chengJi.getKm_id());
            stmt.executeUpdate();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
    public void update(ChengJi chengJi){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc","root","zhuchao");
            String sql=" Update  chengji set fenshu=? where xuesheng_id=? and km_id=? ";
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setObject(1,chengJi.getFenshu());
            stmt.setObject(2,chengJi.getXuesheng_id());
            stmt.setObject(3,chengJi.getKm_id());
            stmt.executeUpdate();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public void find(XueSheng xueSheng,KeCheng keCheng){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc","root","zhuchao");
            String sql ="select xs.xuehao,xs.`name`,xs.sex,kc.km,cj.fenshu from xueshengxinxi xs left join chengji cj on xs.id = cj.xuesheng_id left join kecheng kc on kc.id = cj.km_id where xs.xuehao=? and kc.km = ? ";
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setObject(1,xueSheng.getXuehao());
            stmt.setObject(2,keCheng.getKm());
            ResultSet rs = stmt.executeQuery();
            while(rs.next()){
                Object fenshu = rs.getObject("fenshu");
                Object xuehao = rs.getObject("xuehao");
                Object name = rs.getObject("name");
                Object sex = rs.getObject("sex");
                Object km = rs.getObject("km");
                System.out.println(xuehao+"   "+name+"   "+sex+"   "+km+"   "+fenshu);
            }
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
